﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Pantry.Commons;

namespace Pantry.DataAccess
{
    public class CountryDao
    {
        private SqlConnection _sqlConnection;
        #region Sql Command

        private const string SqlGetAllCountry =
            @"SELECT CountryID,CountryName FROM Country ORDER BY CountryName ASC";
        private const string SqlGetContryIdByName = @"
                                            SELECT TOP 1 CountryId FROM Country WHERE CountryName = @CountryName
                                            ";
        #endregion

        #region Method

        /// <summary>
        /// Selects all country.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectAllCountry()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
            var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlGetAllCountry);
            return (ds.Tables[0] ?? null);
        }
        /// <summary>
        /// Gets the name of the country id by.
        /// </summary>
        /// <param name="countryName">Name of the country.</param>
        /// <returns></returns>
        public int GetCountryIdByName(string countryName)
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
            var parameter = new SqlParameter("@CountryName", SqlDbType.NVarChar) {Value = countryName};
            return (int) SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, SqlGetContryIdByName, parameter);
        }
        #endregion
    }
}
